/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utility;

import DTO.Comment;
import DTO.Comments;
import java.io.File;
import java.io.Serializable;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;

/**
 *
 * @author ZzCFIzZ
 */
public class CommentProcess implements Serializable {

    public static void insertComment(String User_ID, String Comment_Content, String CreateTime, int Place_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "Insert into Comment values(?,?,?,?)";
            stm = con.prepareStatement(sql);
            stm.setString(1, User_ID);
            stm.setString(2, Comment_Content);

            Date d = new Date(System.currentTimeMillis());
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            String result = formatter.format(d.getTime());

            stm.setString(3, result);
            stm.setInt(4, Place_ID);
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void insertProvince(String Province_Name, String Province_PicturePath) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "Insert into Province values(?,?)";
            stm = con.prepareStatement(sql);
            stm.setString(1, Province_Name);
            stm.setString(2, Province_PicturePath);
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

//    public static int total(int Place_ID) {
//        Connection con = null;
//        PreparedStatement stm = null;
//        ResultSet rs = null;
//        int total = 0;
//        try {
//            con = makeConnection();
//            String sql = "Select Count(Comment_ID) as total from Comment where Place_ID=?";
//            stm = con.prepareStatement(sql);
//            stm.setInt(1, Place_ID);
//            rs = stm.executeQuery();
//            System.out.println(rs.getInt("total"));
//            total = rs.getInt("total");
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return total;
//    }
    public static Comments CommentProcess(BigInteger Place_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        Comments cmt = new Comments();
        try {

            con = DBUtils.makeConnection();
            String sql = "Select * From Comment Where Place_ID=? Order By Comment_ID DESC";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Integer.parseInt(Place_ID.toString()));
            rs = stm.executeQuery();
            while (rs.next()) {
                BigInteger Comment_ID = BigInteger.valueOf(rs.getLong("Comment_ID"));
                String User_ID = rs.getString("User_ID");
                String Comment_Content = rs.getString("Comment_Content");
                String CreateTime = rs.getString("CreateTime");
                Comment cm = new Comment(Comment_ID, User_ID, Comment_Content, CreateTime, Place_ID);
                cmt.getComment().add(cm);
            }
            return cmt;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static List loadProvince(int Province_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        Comments cmt = new Comments();
        List list = new ArrayList();
        try {

            con = DBUtils.makeConnection();
            String sql = "Select * From Province Where Province_ID=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Province_ID);
            rs = stm.executeQuery();
            while (rs.next()) {
                String Province_Name = rs.getString("Province_Name");
                String Picture = rs.getString("Province_PicturePath");
                list.add(Province_ID);
                list.add(Province_Name);
                list.add(Picture);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static void deleteComment(int Comment_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "Delete From Comment Where Comment_ID=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Comment_ID);
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void marshallXML(String fileName, int Place_ID) {
        try {
            JAXBContext jaxb = JAXBContext.newInstance(Comments.class);
            Marshaller mar = jaxb.createMarshaller();
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.marshal(CommentProcess(BigInteger.valueOf(Place_ID)), new File(fileName));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static boolean searchProvince(String Province_Name) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        Comments cmt = new Comments();
        try {

            con = DBUtils.makeConnection();
            String sql = "Select * From Province Where Province_Name=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, Province_Name);
            rs = stm.executeQuery();
            while (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

//    public static void copyFile(String sourceFile, String targetFile) {
//        InputStream is = null;
//        OutputStream os = null;
//        try {
//            File aFile = new File(targetFile);
//            File bFile = new File(sourceFile);
//            is = new FileInputStream(aFile);
//            os = new FileOutputStream(bFile);
//            byte[] buffer = new byte[1024];
//            int length;
//            // copy the file content in bytes
//            while ((length = is.read(buffer)) > 0) {
//                os.write(buffer, 0, length);
//            }
//            is.close();
//            os.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
    public static void deleteProvince(int Province_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "Delete From Province Where Province_ID=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Province_ID);
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void UpdateProvince(int Province_ID, String Province_Name, String Province_PicturePath) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "Update Province Set Province_Name=?,Province_PicturePath=? Where Province_ID=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, Province_Name);
            stm.setString(2, Province_PicturePath);
            stm.setInt(3, Province_ID);
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
